package sim.core.dbrecord;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;

import sim.core.utils.BeanUtils;
import sim.core.utils.StringUtils;

/**
 * jdbc数据库操作工具类
 * 
 * @author 许芬勇
 * @date 2015-11-23 11:04:20
 * 
 */
public class DbUtils {
	private static Connection con = null;
	private static PreparedStatement statement = null;
	private static ResultSet rs = null;

	/** 数据源 */
	private static DataSource ds = null;

	public static void init(DataSource dataSource) {
		ds = dataSource;
		getConnection();
	}

	/**
	 * 得到数据连接
	 * 
	 * @return
	 */
	private static Connection getConnection() {
		try {
			if (con == null || con.isClosed()) {
				con = ds.getConnection();
			}
		} catch (SQLException e) {
			System.out.println("连接数据库失败,请检查properties内容是否填写正确！！");
		}
		return con;
	}

	/**
	 * 关闭数据库连接
	 */
	private static void close() {
		try {
			if (!con.isClosed() && con.getAutoCommit()) {
				if (null != rs) {
					rs.close();
				}
				statement.close();
				con.close();
			}
		} catch (SQLException e) {
			System.out.printf("关闭数据库连接失败：%s\n", e.getMessage());
		}
	}

	/**
	 * 增删改(insert,delete,update)
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	private static long executeUpdate(String sql, Object... params) {
		try {
			PreparedStatement statement = createStatement(sql, params);
			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			if (rs.next()) {
				// 返回新增记录的自增ID
				return rs.getLong(1);
			} else {
				// 表示成功
				return 1L;
			}
		} catch (SQLException e) {
			System.out.printf("执行SQL语句异常：%s\n", e.getMessage());
		} finally {
			close();
		}
		// 表示失败
		return 0L;
	}

	/**
	 * 执行查询语句
	 * 
	 * @author Feny
	 * @date 2018年6月1日下午5:26:23
	 * @param sql
	 * @param params
	 * @return
	 */
	private static ResultSet executeQuery(String sql, Object... params) {
		try {
			return createStatement(sql, params).executeQuery();
		} catch (SQLException e) {
			System.out.printf("执行SQL语句异常：%s\n", e.getMessage());
		}
		return null;
	}

	/**
	 * 统一数据绑定方法
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private static PreparedStatement createStatement(String sql, Object... params) throws SQLException {
		System.out.printf("执行SQL：%s\n参数值：%s\n\n", sql.toString(), Arrays.toString(params));
		statement = (PreparedStatement) getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		if (null != params) {
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i + 1, params[i]);
			}
		}
		return statement;
	}

	/**
	 * 查询记录，返回list集合
	 * 
	 * @where LIKE CONCAT('%',?,'%')
	 * @param sql
	 * @param params
	 * @param bean
	 * @return
	 */
	public static List<Map<String, Object>> queryForList(String sql, Object[] params) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		try {
			rs = executeQuery(sql, params);
			// 结果集元数据
			ResultSetMetaData resultSetMetaData = (ResultSetMetaData) rs.getMetaData();
			int count = resultSetMetaData.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= count; i++) {
					map.put(resultSetMetaData.getColumnLabel(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return list;
	}

	/**
	 * Map参数为key-value <br>
	 * key对应数据库字段<br>
	 * value为要查询的数据
	 */
	public static <T> List<T> queryForList(Map<String, Object> params, Class<?> bean) {
		return queryForList(getTableName(bean), params, bean);
	}

	public static <T> List<T> queryForList(String table, Map<String, Object> params, Class<?> bean) {
		StringBuilder sql = new StringBuilder(getSelectSql(table));
		return queryForList(getWhereSql(sql, params.keySet()), params.values().toArray(), bean);
	}

	/**
	 * 查询结果，返回list集合javabean
	 * 
	 * @where LIKE CONCAT('%',?,'%')
	 * @param sql
	 * @param params
	 * @param bean
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> queryForList(String sql, Object[] params, Class<?> bean) {
		List<T> list = new ArrayList<T>();
		List<Map<String, Object>> queryForList = queryForList(sql, params);
		for (Map<String, Object> map : queryForList) {
			T obj = (T) BeanUtils.mapToBean(map, bean);
			list.add(obj);
		}
		return list;
	}

	public static Map<String, Object> queryForMap(String table, Map<String, Object> params) {
		StringBuilder sql = new StringBuilder(getSelectSql(table));
		return queryForMap(getWhereSql(sql, params.keySet()), params.values().toArray());
	}

	/**
	 * 查询一条记录，返回map集合
	 * 
	 * @where LIKE CONCAT('%',?,'%')
	 * @param sql
	 * @param params
	 * @param bean
	 * @return
	 */
	public static Map<String, Object> queryForMap(String sql, Object[] params) {
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			rs = executeQuery(sql, params);

			/**
			 * 获取记录数 1.定位到最后一行; 2.获取当前行行数; （3.如果还要用结果集，把指针重新移到初始化的位置）
			 */
			// 1.定位到最后一行
			rs.last();
			// 2.获取当前行行数
			int row = rs.getRow();
			// 3.把指针重新移到初始化的位置
			rs.beforeFirst();

			if (row > 1) {
				throw new ArrayIndexOutOfBoundsException(String.format("错误结果大小：预期1，实际%s", row));
			} else {
				ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
				int count = rsmd.getColumnCount();
				while (rs.next()) {
					for (int i = 1; i <= count; i++) {
						map.put(rsmd.getColumnName(i), rs.getObject(i));
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return map;
	}

	public static <T> T queryForBean(Map<String, Object> params, Class<?> bean) {
		return queryForBean(getTableName(bean), params, bean);
	}

	public static <T> T queryForBean(String table, Map<String, Object> params, Class<?> bean) {
		StringBuilder sql = new StringBuilder(getSelectSql(table));
		return queryForBean(getWhereSql(sql, params.keySet()), params.values().toArray(), bean);
	}

	/**
	 * 查询结果，返回javabean
	 * 
	 * @where LIKE CONCAT('%',?,'%')
	 * @param sql
	 * @param params
	 * @param bean
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> T queryForBean(String sql, Object[] params, Class<?> bean) {
		Map<String, Object> map = queryForMap(sql, params);
		T t = null;
		if (map.size() > 0) {
			t = (T) BeanUtils.mapToBean(map, bean);
		}
		return t;
	}

	/**
	 * 统计数量
	 * 
	 * @where LIKE CONCAT('%',?,'%')
	 * @param sql
	 * @des (select count(*) from table)
	 * @param params
	 * @return
	 */
	public static long count(String sql, Object[] params) {
		long count = 0;
		sql.toUpperCase();
		try {
			if (StringUtils.containsIgnoreCase(sql, "COUNT")) {
				rs = executeQuery(sql, params);
			} else {
				// 查询语句转换为统计语句
				sql = sql.replaceFirst("SELECT.*?FROM", "SELECT COUNT(*) FROM");
				rs = executeQuery(sql, params);
			}
			while (rs.next()) {
				count = rs.getLong(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return count;
	}

	public static Page queryForPage(String sql, Object[] params, Page page) {
		return queryForPage(sql, params, page.getPage(), page.getPageSize());
	}

	/**
	 * 分页查询，返回List<Map<String, Object>>的结果集
	 * 
	 * @where LIKE CONCAT('%',?,'%')
	 * @param sql
	 * @param params
	 * @param page
	 * @param pageSize
	 * @return
	 */
	public static Page queryForPage(String sql, Object[] params, long page, long pageSize) {
		long totalSize = count(sql, params);
		Page result = new Page(page, totalSize, pageSize);

		if (result.getTotalPage() < result.getPage())
			return result;

		pageSize = pageSize == 0 ? new Page().getPageSize() : pageSize;

		sql = String.format("%s LIMIT %s,%s", sql, (page - 1) * pageSize, pageSize);
		List<Map<String, Object>> data = queryForList(sql, params);

		result.setData(data);
		return result;
	}

	/**
	 * 插入数据<br>
	 * 数据库表名和javabean名一致时使用
	 */
	public static <T> long insert(T bean) {
		return insert(bean, getTableName(bean.getClass()));
	}

	/**
	 * 插入数据
	 */
	public static <T> long insert(T bean, String table) {
		if (null == bean && StringUtils.isBlank(table)) {
			throw new NullPointerException("插入数据失败，插入数据和表名不能为空");
		}

		Map<String, Object> map = BeanUtils.beanToMap(bean);
		StringBuilder sql = new StringBuilder(String.format("INSERT INTO %s ", table));
		StringBuilder values = new StringBuilder();
		Object[] params = map.values().toArray();

		// map keys to String is sql columns
		// map keys to String is [id,name], so it needs to be remove []
		sql.append(String.format("(%s)", String.join(",", map.keySet())));

		// values
		for (int i = 0, size = map.size(); i < size; i++) {
			values.append("?,");
		}
		sql.append(String.format(" VALUES(%s)", values.deleteCharAt(values.length() - 1)));
		return insert(sql.toString(), params);
	}

	public static Long insert(String sql, Object[] params) {
		return executeUpdate(sql, params);
	}

	public static <T> Long update(T bean) {
		return update(bean, getTableName(bean.getClass()));
	}

	/**
	 * 更新数据
	 * 
	 * @param bean
	 * @param table
	 * @return
	 */
	public static <T> Long update(T bean, String table) {
		Map<String, Object> map = BeanUtils.beanToMap(bean);
		StringBuilder sql = new StringBuilder(String.format("UPDATE %s SET ", table));
		List<Object> params = new ArrayList<>();

		// append set
		for (Entry<String, Object> entry : map.entrySet()) {
			if (entry.getValue() != null && !entry.getKey().equals("id")) {
				sql.append(String.format("%s=?,", entry.getKey()));
				params.add(entry.getValue());
			}
		}

		// remove last ","
		sql.deleteCharAt(sql.length() - 1);
		// append where
		sql.append(" WHERE id=?");
		params.add(map.get("id"));

		return update(sql.toString(), params.toArray());
	}

	public static Long update(String sql, Object[] params) {
		return executeUpdate(sql, params);
	}

	public static Long delete(String sql, Object[] params) {
		return executeUpdate(sql, params);
	}

	/**
	 * 指定WHERE条件字段名和值删除数据 <br/>
	 * Map的key是表中的字段名
	 * 
	 * @param table
	 * @param params
	 * @return
	 */
	public static Long delete(String table, Map<String, Object> params) {
		StringBuilder sql = new StringBuilder(String.format("DELETE FROM %s", table));
		return delete(getWhereSql(sql, params.keySet()), params.values().toArray());
	}

	/**
	 * 通过javaBean获取数据库表名
	 * 
	 * @author Feny
	 * @date 2018年6月1日下午5:59:01
	 * @param bean
	 * @return
	 */
	private static String getTableName(Class<?> bean) {
		char[] charArray = bean.getSimpleName().toCharArray();
		charArray[0] += 32;
		return String.valueOf(charArray);
	}

	private static String getSelectSql(String table) {
		return String.format("SELECT * FROM %s", table);
	}

	private static String getWhereSql(StringBuilder sql, Set<String> keySet) {
		if (keySet.size() == 0) {
			return sql.toString();
		}
		sql.append(" WHERE");
		for (String key : keySet) {
			sql.append(String.format(" %s=? AND", key));
		}
		return StringUtils.removeEnd(sql.toString(), "AND");
	}

	private static void doBegin() {
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
		} catch (SQLException e) {
			System.out.printf("连接数据库错误：%s\n", e.getMessage());
		}
	}

	private static void doCommit() {
		try {
			con.commit();
		} catch (SQLException e) {
			System.out.printf("事务提交错误：%s\n", e.getMessage());
		} finally {
			try {
				con.setAutoCommit(true);
			} catch (SQLException e) {
				System.out.printf("设置自动提交事务错误：%s\n", e.getMessage());
			}
			close();
		}
	}

	private static void doRollback() {
		try {
			con.rollback();
		} catch (SQLException e) {
			System.out.printf("事务回滚错误：%s\n", e.getMessage());
		} finally {
			close();
		}
	}

	/**
	 * 使用事务
	 * 
	 * @author Feny
	 * @date 2018年7月24日上午10:27:40
	 * @param tx
	 */
	public static void useTx(Tx tx) {
		try {
			doBegin();
			tx.run();
		} catch (Exception e) {
			doRollback();
			e.printStackTrace();
		}
		doCommit();
	}

}
